from flask import Blueprint, request, jsonify, send_from_directory, send_file
from utilsapp.mysql_con import db_connect
from utilsapp.mysql_con import db_operation,db_fetchone,db_pagelist
from utilsapp.common import parsing_permissions,assign_value,parsing_data_id
import os

selfreport = Blueprint('selfreport', __name__)


# 新版 以工厂 为单位展示记录
@selfreport.route("/getlist",methods=['post'])
def a():
    interval_time = 2
    a = request.json
    scope_id = assign_value(a, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    query = ''
    if scoped!='':
      query += f" and {scoped} "

    sql = f"""SELECT 
    a.factory_id, 
    b.factory_name, 
    b.num AS allNum, 
    IFNULL(a.num, 0) AS errNum, 
    a.start_time, 
    a.end_time 
FROM
    (
        SELECT 
            s.factory_id, 
            COUNT(DISTINCT sl.sensor_id) AS num, 
            MAX(sl.new_time) AS end_time, 
            MIN(sl.start_time) AS start_time 
        FROM 
            selfdiagnose sl
            LEFT JOIN sensor s ON s.id = sl.sensor_id
        WHERE 
            sl.state > 0
        GROUP BY 
            s.factory_id
    ) a
    RIGHT JOIN
    (
        SELECT 
            s.factory_id, 
            r.name AS factory_name, 
            COUNT(s.id) AS num 
        FROM 
            sensor s
            LEFT JOIN regionlevel r ON r.id = s.factory_id 
        WHERE 1=1
           {query}
        GROUP BY 
            s.factory_id, r.name
    ) b ON a.factory_id = b.factory_id"""
    sql_del = f"DELETE FROM selfdiagnose WHERE new_time < DATE_SUB(NOW(), INTERVAL {interval_time} HOUR)"
    res=db_operation(sql_del)

    return db_pagelist(sql,a['page'])
    

@selfreport.route("/factory/details",methods=['post'])
def a1():
    a=request.json
    factory_id = assign_value(a,"factory_id")
    size = assign_value(a,"size",20)
    query =""
    if factory_id!="":
        query +=f" where s.factory_id = {factory_id} "
    else:
        return {
            "code":-1,
            "msg":"factory_id is null",
            "data":[]
            }
    sql = f"""SELECT s.id as sensor_id,s.sensor_name,d.id as device_id,d.device_name,sl.* from selfdiagnose sl
left JOIN sensor s on s.id = sl.sensor_id
LEFT JOIN device d on d.id = s.device_id {query} and sl.new_time > DATE_SUB(NOW(), INTERVAL 1 HOUR) and sl.state>0
order by s.id"""

    res =db_pagelist(sql,a['page'],size)
    for i in res['data']:
        arr = i['desc'].split("|")
        try:
            i['feature_key'] = arr[4]
            i['content'] = arr[3] +" "+arr[4] +" "+arr[2]
        except:
            i['feature_key'] = ""
            i['content'] = ""
    return res


